﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SqlMiM.Common.Model;
using SqlMiM.SqlIndexes.Model;
using SqlMiM.Common;
using SqlMiM.Common.Trace;
using System.Data;
using SqlMiM.Common.Core;
using SqlMiM.SqlIndexes.Options;

namespace SqlMiM.SqlIndexes.Core
{
    public class IndexesService
    {

        #region Singleton

        private static IndexesService current;

        public static IndexesService Current
        {
            get
            {
                if (current == null)
                {
                    current = new IndexesService();
                }

                return current;
            }
        }

        #endregion


        public List<FragmentedIndex> GetFragmentedIndexes(Database db)
        {

            SqlConnection connection = Context.Current.GetConnection(db.Name);
            SqlDataReader reader = null;
            List<FragmentedIndex> fragIndexes = null;

            try
            {

                var selectServerInfosCommand = this.GetSelectDatabasesMostFragmentedIndexesCommand();
                selectServerInfosCommand.Connection = connection;

                connection.Open();

                reader = selectServerInfosCommand.ExecuteReader();


                if (reader.HasRows)
                {
                    fragIndexes = new List<FragmentedIndex>();

                    while (reader.Read())
                    {
                        FragmentedIndex ind = new FragmentedIndex();

                        // Get principals informations
                        Index.FillFromReader(reader, ind);

                        ind.DatabaseId = db.Id;
                        ind.DatabaseName = db.Name;

                        ind.Avg_fragmentation_in_percent = reader["avg_fragmentation_in_percent"] != System.DBNull.Value ? (Double)reader["avg_fragmentation_in_percent"] : 0d;
                        ind.Last_user_seek = reader["last_user_seek"] != System.DBNull.Value ? (DateTime?)reader["last_user_seek"] : (DateTime?)null;
                        ind.Last_user_scan = reader["last_user_scan"] != System.DBNull.Value ? (DateTime?)reader["last_user_scan"] : (DateTime?)null;
                        ind.Last_user_update = reader["last_user_update"] != System.DBNull.Value ? (DateTime?)reader["last_user_update"] : (DateTime?)null;
                        ind.Page_count = reader["page_count"] != System.DBNull.Value ? (Int64)reader["page_count"] : 0;
                        ind.Rows = reader["rows"] != System.DBNull.Value ? (Int32)reader["rows"] : 0;
                        ind.Size = reader["size"] != System.DBNull.Value ? (Int32)reader["size"] : 0;
                        ind.User_scans = reader["user_scans"] != System.DBNull.Value ? (Int64)reader["user_scans"] : 0;
                        ind.User_seeks = reader["user_seeks"] != System.DBNull.Value ? (Int64)reader["user_seeks"] : 0;

                        fragIndexes.Add(ind);
                    }
                }
                reader.Close();

                connection.Close();

                return fragIndexes;

            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }

            return null;

        }

        public List<PotentialIndex> GetPotentialIndexes(Database db)
        {

            SqlConnection connection = Context.Current.GetConnection(db.Name);
            SqlDataReader reader = null;
            List<PotentialIndex> lstIndexes = null;

            try
            {

                var sqlCmd = this.GetSelectDatabasesPotentialIndexesCommand(db.Id);
                sqlCmd.Connection = connection;

                connection.Open();

                reader = sqlCmd.ExecuteReader();


                if (reader.HasRows)
                {
                    lstIndexes = new List<PotentialIndex>();

                    while (reader.Read())
                    {
                        PotentialIndex ind = new PotentialIndex();


                        ind.DatabaseId = db.Id;
                        ind.DatabaseName = db.Name;

                        ind.DatabaseSchemaTable = reader["database_schema_table"] as String;
                        ind.IndexedColumns = reader["indexed_columns"] as String;
                        ind.IncludedColumns = reader["included_columns"] as String;

                        ind.PotentialUserSeeks = reader["potential_user_seeks"] != System.DBNull.Value ? (Int64)reader["potential_user_seeks"] : (Int64)0;
                        ind.PotentialLastUserSeek = reader["potential_last_user_seek"] != System.DBNull.Value ? (DateTime?)reader["potential_last_user_seek"] : (DateTime?)null;

                        ind.PotentialUserScans = reader["potential_user_scans"] != System.DBNull.Value ? (Int64)reader["potential_user_scans"] : (Int64)0;
                        ind.PotentialLastUserScan = reader["potential_last_user_scan"] != System.DBNull.Value ? (DateTime?)reader["potential_last_user_scan"] : (DateTime?)null;

                        ind.PotentialImpact = reader["potential_impact"] != System.DBNull.Value ? (Int32)reader["potential_impact"] : 0;
                        ind.PotentialReducedAvgUserCost = reader["potential_reduced_avg_user_cost"] != System.DBNull.Value ? (Double)reader["potential_reduced_avg_user_cost"] : 0d;
                        ind.PotentialPctGainUserImpact = reader["potential_pct_gain_user_impact"] != System.DBNull.Value ? (Double)reader["potential_pct_gain_user_impact"] : 0;

                        
                        lstIndexes.Add(ind);
                    }
                }
                reader.Close();

                connection.Close();

                return lstIndexes;

            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }

            return null;

        }

        public List<UnusedIndex> GetUnusedIndexes(Database db)
        {

            SqlConnection connection = Context.Current.GetConnection(db.Name);
            SqlDataReader reader = null;
            List<UnusedIndex> lstIndexes = null;

            try
            {

                var selectServerInfosCommand = this.GetSelectDatabasesUnusedIndexesCommand();
                selectServerInfosCommand.Connection = connection;

                connection.Open();

                reader = selectServerInfosCommand.ExecuteReader();


                if (reader.HasRows)
                {
                    lstIndexes = new List<UnusedIndex>();

                    while (reader.Read())
                    {
                        UnusedIndex ind = new UnusedIndex();

                        // Get principals informations
                        Index.FillFromReader(reader, ind);

                        ind.DatabaseId = db.Id;
                        ind.DatabaseName = db.Name;

                        lstIndexes.Add(ind);
                    }
                }
                reader.Close();

                connection.Close();

                return lstIndexes;

            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }

            return null;

        }

        internal SqlCommand GetSelectDatabasesMostFragmentedIndexesCommand()
        {
            StringBuilder sb = new StringBuilder();
            SqlCommand selectServerInfosCommand;

            var conf = Context.Current.Configuration[IndexOptionsPage.NAME];

            Byte avgFragPct = (Byte)conf.GetProperty(IndexOptionsPage.AVG_PCT_FRAG_PROPERTY, IndexOptionsPage.AVG_PCT_FRAG_DEFAULT_VALUE);
            Int32 maxIndexCount = (Int32)conf.GetProperty(IndexOptionsPage.MAX_INDEX_COUNT_PROPERTY, IndexOptionsPage.MAX_INDEX_COUNT_DEFAULT_VALUE);

            sb.AppendLine("    WITH ReallyUsedIndexes AS ");
            sb.AppendLine("    ( ");
            sb.AppendLine(String.Format("        SELECT top {0} i.id,i.name,i.indid, ((8* i.reserved)/1024) as size, i.rows, ", maxIndexCount.ToString()));
            sb.AppendLine("              [user_seeks] ,[user_scans] ,[last_user_seek], [last_user_scan], [last_user_update] ");
            sb.AppendLine("        FROM sysindexes i ");
            sb.AppendLine("        Inner Join sys.dm_db_index_usage_stats ius ON i.id = ius.object_id AND i.indid = ius.index_id ");
            sb.AppendLine("        WHERE i.dpages > 0   ");
            sb.AppendLine("       ORDER BY size desc, rows desc ");
            sb.AppendLine("    ) ");
            sb.AppendLine("    SELECT  ");
            sb.AppendLine("          OBJECT_SCHEMA_NAME(SDDIPS.[object_id]) + '.' + OBJECT_NAME(SDDIPS.[object_id]) AS [object_name],  ");
            sb.AppendLine("          SI.*,  ");
            sb.AppendLine("          CAST(INDEXPROPERTY(SI.object_id, SI.name, N'IsPadIndex') AS bit) AS pad_index, ");
            sb.AppendLine("          CAST(INDEXPROPERTY(SI.object_id, SI.name, N'IsFulltextKey') AS bit) AS is_full_text_key, ");
            sb.AppendLine("          SDDIPS.[index_type_desc], SDDIPS.[avg_fragmentation_in_percent], ");
            sb.AppendLine("          ReallyUsedIndexes.size, ReallyUsedIndexes.rows, ");
            sb.AppendLine("          SDDIPS.page_count,  ");
            sb.AppendLine("           ReallyUsedIndexes.[user_seeks] ,ReallyUsedIndexes.[user_scans] , ");
            sb.AppendLine("          ReallyUsedIndexes.[last_user_seek] ,ReallyUsedIndexes.[last_user_scan], ReallyUsedIndexes.[last_user_update] ");
            sb.AppendLine("        FROM sys.[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'Detailed') SDDIPS ");
            sb.AppendLine("           inner Join sys.[indexes] SI ON SDDIPS.[object_id] = SI.[object_id]AND SDDIPS.[index_id] = SI.[index_id] ");
            sb.AppendLine("          inner join ReallyUsedIndexes ON ReallyUsedIndexes.id = SI.[object_id]AND ReallyUsedIndexes.indid = SI.[index_id] ");
            sb.AppendLine("        WHERE  ");
            sb.AppendLine("       ( ");
            sb.AppendLine("               SDDIPS.[page_count] > 2 ");
            sb.AppendLine("               AND SDDIPS.[index_id] > 0 ");
            sb.AppendLine(String.Format("               AND SDDIPS.[avg_fragmentation_in_percent] > {0} ", avgFragPct.ToString()));
            sb.AppendLine("               AND SDDIPS.index_level = 0 ");
            sb.AppendLine("    ) ");
            sb.AppendLine("    ORDER BY SDDIPS.[avg_fragmentation_in_percent] desc;  ");

            selectServerInfosCommand = new SqlCommand();
            selectServerInfosCommand.CommandType = CommandType.Text;
            selectServerInfosCommand.CommandText = sb.ToString();

            return selectServerInfosCommand;

        }

        internal SqlCommand GetSelectDatabasesUnusedIndexesCommand()
        {
            StringBuilder sb = new StringBuilder();
            SqlCommand command;

            var conf = Context.Current.Configuration[IndexOptionsPage.NAME];

            sb.AppendLine("select  OBJECT_SCHEMA_NAME(i.object_id) + '.' +  OBJECT_NAME(i.object_id) as object_Name, i.*, c.index_columns, ");
            sb.AppendLine("CAST(INDEXPROPERTY(i.object_id, i.name, N'IsPadIndex') AS bit) AS pad_index, ");
            sb.AppendLine("CAST(INDEXPROPERTY(i.object_id, i.name, N'IsFulltextKey') AS bit) AS is_full_text_key ");
            sb.AppendLine("from sys.indexes i   ");
            sb.AppendLine("left outer join sys.dm_db_index_usage_stats d on d.object_id = i.object_id and i.index_id = d.index_id   ");
            sb.AppendLine("left outer join  ");
            sb.AppendLine("	(select distinct object_id, index_id,  ");
            sb.AppendLine("		stuff((SELECT   ','+ col_name(object_id,column_id ) as 'data()'  ");
            sb.AppendLine("		FROM   sys.index_columns t2  where t1.object_id =t2.object_id  and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')     ");
            sb.AppendLine("	 as 'index_columns' FROM  sys.index_columns t1  ) c on ");
            sb.AppendLine("c.index_id = i.index_id and c.object_id = i.object_id ");
            sb.AppendLine("where objectproperty(i.object_id, 'IsIndexable') = 1 -- Check if it's a potential Index ");
            sb.AppendLine("and d.index_id is null  ");
            sb.AppendLine("and i.type_desc not in ('heap','clustered')  ");
            sb.AppendLine("order by Object_Name     ");

            command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = sb.ToString();

            return command;

        }

        internal SqlCommand GetSelectDatabasesPotentialIndexesCommand(Int32 databaseId)
        {
            StringBuilder sb = new StringBuilder();
            SqlCommand command;

            var conf = Context.Current.Configuration[IndexOptionsPage.NAME];


            sb.AppendLine("SELECT	mid.statement AS [database_schema_table], ");
            sb.AppendLine("	    COALESCE(mid.equality_columns + ', ', SPACE(0)) + COALESCE(mid.inequality_columns, SPACE(0)) as indexed_columns,  ");
            sb.AppendLine("	    mid.included_columns,  ");
            sb.AppendLine("	    migs.user_seeks as potential_user_seeks,  ");
            sb.AppendLine("	    migs.last_user_seek as potential_last_user_seek,  ");
            sb.AppendLine("	    migs.user_scans as potential_user_scans,  ");
            sb.AppendLine("	    migs.last_user_scan as potential_last_user_scan,  ");
            sb.AppendLine("	    cast((migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_scans + migs.user_seeks)) as Int) as potential_impact, ");
            sb.AppendLine("	    migs.avg_total_user_cost as potential_reduced_avg_user_cost,  ");
            sb.AppendLine("	    migs.avg_user_impact as potential_pct_gain_user_impact ");
            sb.AppendLine("FROM		sys.dm_db_missing_index_details AS mid ");
            sb.AppendLine("INNER JOIN	sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle ");
            sb.AppendLine("INNER JOIN	sys.dm_db_missing_index_group_stats  AS migs ON mig.index_group_handle=migs.group_handle ");
            sb.AppendLine("WHERE mid.database_id = @DatabaseId ");
            sb.AppendLine("ORDER BY potential_impact ");

            command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = sb.ToString();

            SqlParameter p = new SqlParameter("@DatabaseId", SqlDbType.SmallInt);
            p.Value = (Int16)databaseId;

            command.Parameters.Add(p);

            return command;

        }

    }
}
